package sim.ring.tower;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Date;
import java.util.Vector;

import org.apache.log4j.Logger;

import sim.ring.technical.data.DatabaseConnection;

public class BellQueryBuilder {
	private static Logger log = Logger.getLogger("sim.ring.tower");
	
	public static void saveBell(Bell bell) {
		DatabaseConnection db = new DatabaseConnection();
		try {
			db.connectToDB("RingingSimDB");
		} catch (SQLException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		
		Vector<Object> params = new Vector<Object>();
		String sql = "";
		int id = bell.getId();
		
		if(id>0) {
			//update
			sql = "UPDATE bells SET "
					+"tower_id=?,"
					+"number=?,"
					+"weight=?,"
					+"diameter=?,"
					+"note=?,"
					+"date=\'01.01.1900\'," //TODO:build this into bell class
					+"foundry_id=1," //TODO:build this into bell class
					+"audio_file=? "
					+ "WHERE id=?";
						
			params.add(new Integer(bell.getTower().getId()));
			params.add(new Integer(bell.getNumber()));
			params.add(bell.getWeight());
			params.add(new Float(bell.getDiameter()));
			params.add(bell.getNote());
			params.add(bell.getAudioFileName());
			params.add(new Integer(bell.getId()));
		} else {
			//insert			
			sql = "INSERT INTO bells ("
					+"tower_id,"
					+"number,"
					+"weight,"
					+"diameter,"
					+"note,"
					+"date,"
					+"foundry_id,"
					+"audio_file) "
					+"VALUES ("
					+"?,"
					+"?,"
					+"?,"
					+"?,"
					+"?,"
					+"\'01.01.1900\',"
					+"1,"
					+"?" 
					+")";
				
			params.add(new Integer(bell.getTower().getId()));
			params.add(new Integer(bell.getNumber()));
			params.add(bell.getWeight());
			params.add(new Float(bell.getDiameter()));
			params.add(bell.getNote());
			params.add(bell.getAudioFileName());
		}
		
		try {
			int key = db.issueNonQueryStatement(sql,params);
			if(id==-1) {
				id=key;
				bell.setId(id);
			}
		} catch (SQLException e) {
			//TODO
			log.error(e.getMessage());
		}
		db.closeDB();
		log.info("Bell " + bell.getNumber() + " saved with id " + id);
	}
	
	public static Bell loadBell(int towerId, int number) {
		int id=-1;
		String weight="";
		String note="";
		String audioFile="";
		float diameter=-1;
		int foundryId=-1;
		Date date;
		
		DatabaseConnection db = new DatabaseConnection();
		try {
			db.connectToDB("RingingSimDB");
		} catch (SQLException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		
		
		String sql = "SELECT * FROM bells " 
					+ "WHERE tower_id=? "
					+ "AND number=?";
		Vector<Object> params = new Vector<Object>();
		params.add(new Integer(towerId));
		params.add(new Integer(number));
		
		ResultSet rs;
		try {
			rs = db.issueQueryStatement(sql, params);
			rs.next();
			id=rs.getInt(1);
			weight=rs.getString(4);
			diameter=rs.getFloat(5);
			note=rs.getString(6);
			date=null;
			foundryId=rs.getInt(8);
			audioFile=rs.getString(9);
		} catch (SQLException e) {
			//TODO
			log.error(e.getMessage());
		} finally {
			//TODO: close everything		
		}
		Bell bell = new Bell(number,audioFile,weight,diameter,note);
		bell.setId(id);
		db.closeDB();
		return bell;
	}
}
